Identificando e resolvendo os Eventos: Cursor: Pin S e Cursor: Pin S wait on X
Por Victor Armbrust,
Postado en janeiro 2013
Gerenciamento otimizado de memória, monitoramento de processos, performance satisfatória em queries e processos administrativos, backup em tempo recorde, relatórios executados em minutos, planos de execução extremamente performáticos, enfimm garantir performance em um Banco de Dados Oracle é sempre uma das muitas tarefas de um DBA Oracle.
No Oracle Database existem diversos eventos que ocorrem enquanto o Banco de Dados está em execução. Alguns deles podem ou não impactar o tempo de resposta de processos, gerar contenções ou concorrências ou ainda algum tipo de "hang"/ travamento no ambiente. O importante é entender o funcionamento destes eventos no Banco de Dados e encontrar assim a melhor maneira para resolver problemas de performance.
O que são Eventos de Espera (Wait Events)?
Em algum determinado momento, processos do Oracle estão ocupados executando alguma instrução específica ou aguardando que algo específico aconteça. Por exemplo, a execução de um cálculo qualquer dentro de bloco PL/SQL significa um determinado processo pode estar utilizando CPU, o que significa que este processo pode estar apenas em "busy" não necessariamente em "wait", apenas em execução. Por outro lado, um processo como o LGWR pode estar aguardando que o Sistema Operacional confirme que uma informação do Log Buffer foi gravado com sucesso da memória para os online redo log files em disco, neste caso o processo LGWR estaria em "wait" não em "busy".
Na maioria das vezes, o próprio Oracle encontra a solução ou tratativa correta para cada evento de espera, sendo assim, a monitoração preventiva de eventos que ocorrem no banco de dados pode ajudar a identificar eventos com longa espera ou travados. Porém, em alguns casos, podem ocorrer eventos de espera no Banco de Dados e consequentemente a intervenção do DBA será necessária,
Afinal, o que é o evento: Cursor: Pin S wait on X?
Em alguns casos onde aplicações utilizam um alto número de cursores abertos simultaneamente, uma sessão pode requisitar o acesso a uma área de memória para utilização de um cursor (shared mutex pin) em um objeto que já está sendo utilizado por outro cursor em modo exclusivo (exclusive mutex pin).
O Conceito de "mutex" foi implementado no Oracle Database 10gR2, com o objetivo de proteger dados (ou recursos) de acesso concorrente. (Mutual Exclusion). Em outras palavras: Toda vez que o Oracle obtém de um mutex, seu valor é incrementado. Toda vez que o mutex é liberado, seu valor é decrementado.
O evento "Cursor: Pin S wait on X" tem ligação direta com o evento "Cursor Pin S". Veja abaixo:
Cursor: Pin S : Este é um evento de espera que acontece quando uma sessão precisa de um Mutex específico para um cursos específico (em modo Share). Neste caso, o Oracle atualiza contadores internos para obter o mutex desejado.
É importante lembrar que o acesso aos mutexes/contadores não são concorrentes, ou seja, se outras sessões tentarem obter um mutex que já esteja em uso, então o evento "cursor: pin s" irá ocorrer.
Cursor: Pin S wait on X: Este é um evento de espera que ocorre quando o Oracle não consegue obter um mutex em modo compartilhado (share) pois existe outra sessão utilizando o mesmo mutex em modo exclusivo (exclusive X).
Resumo:
Cursor: Pin S : Ocorre quando o Oracle precisa de um mutex específico para um cursor específico, mas não consegue obter pois não consegue atualizar seus contadores internos (Não porque outra sessão está utilizando o mutex referenciado em modo exclusivo)
Cursor: Pin S wait on X : Ocorre quando o Oracle precisa de um mutex específico em modo Share(S) para um cursor específico, porém outra sessão está usando o mesmo mutex em modo exclusivo(X).
Onde encontrar informações sobre eventos de espera?
Informações sobre eventos de espera podem ser encontradas em 3 (três) views:
- V$SESSION_WAIT mostra os eventos para cada sessão que estavam em "wait" ou estão em "wait"
- V$SYSTEM_EVENT mostra o número total de vezes que todas as sessões aguardaram por eventos dessa view.
- V$SESSION_EVENT similar a view V$SYSTEM_EVENT, mas mostra cada evento de wait para sessões atuais.
Exemplo de sessões em wait e do evento Cursor: Pin S wait on X:
SID SERIAL# EVENT WAIT STATUS MACHINE
---- ------- ------------------------------- ------ ---------- ---------
3257 101 db file scattered read 0 ACTIVE servidor1
3258 99 db file read by other session 2,555 ACTIVE servidor1
3259 111 PX Deq: read by other session 33,555 ACTIVE servidor1
3260 110 PX Deq: Execution Msg 0 ACTIVE servidor1
3261 102 PX Deq: Execution Msg 0 ACTIVE servidor1
3262 114 db file scattered read 0 ACTIVE servidor1
3289 86 db file scattered read 0 ACTIVE servidor1
3267 169 db file scattered read 0 ACTIVE servidor1
3268 104 db file Cursor Pin S Wait on X 1,321 ACTIVE servidor1
3269 96 PX Deq: Cursor Pin S Wait on X 1,321 ACTIVE servidor1
3270 10 PX Deq: Execute Reply 0 ACTIVE servidor1
3279 121 PX Deq: Execution Msg 0 ACTIVE servidor1
3265 317 PX Deq: Execution Msg 0 ACTIVE servidor1
3291 307 PX Deq: i/o slave wait 2,531 ACTIVE servidor1
3292 455 PX Deq: i/o slave wait 2,531 ACTIVE servidor1
3298 322 PX Deq: i/o slave wait 2,531 ACTIVE servidor1
Mais informações sobre sessões em espera pelo evento Cursor: Pin S wait on X
Para verificar mais informações sobre sessões em espera por cursores, pode-se utilizar os scripts a seguir:
- Verificar sessões que estão bloqueando outras sessões
select p2raw, p2/power(16,8) blocking_sid, p1 mutex_id, sid blocked_sid
from v$session
where event like 'cursor:%'
and state='WAITING' /
- Verificar histórico de sessões que aguardaram pelo evento
select requesting_session, blocking_session, sleep_timestamp, mutex_type from v$mutex_sleep_history /
- Identificar sessões que estão aguardando
select * from v$mutex_sleep /
- Identificar sessões com maior tempo de Banco de Dados (Load) por espera em eventos de Cursor Pin%
select EVENT, WAIT_TIME_MILLI as "WAIT(MS)", WAIT_COUNT,
round((WAIT_TIME_MILLI*100)/(select sum(WAIT_TIME_MILLI) from
v$event_histogram),3) as "TOTAL DB WAIT(%)"
from v$event_histogram
where WAIT_TIME_MILLI > 512
and event like '%cursor%'
group by EVENT, WAIT_TIME_MILLI, WAIT_COUNT
order by WAIT_TIME_MILLI desc
/
- Identificar SQLID com evento de espera em Cursor Pin%
select count(*), sql_id,sql_child_number,session_state,blocking_session_status,event,wait_class
from DBA_HIST_ACTIVE_SESS_HISTORY
where snap_id between 18085 and 18086
and event like '%cursor: pin S%'
group by sql_id,sql_child_number,session_state,blocking_session_status,event,wait_class
/
Verificando informações sobre Cursores e Sessões com espera em Cursor Pin
Uma vez identificadas as sessões que estão com espera nesse evento, pode-se obter mais informações ou coletas sobre as mesmas. Veja a seguir:
- Identificar consumo pelo SQLID que está em espera por evento Cursor Pin%
select sql_id,optimizer_cost o_cost,optimizer_mode o_mode ,SHARABLE_MEM mem ,
version_count ver_c,fetches_total fetc_t ,END_OF_FETCH_COUNT_TOTAL cnt_total,
EXECUTIONS_TOTAL exe ,PARSE_CALLS_TOTAL pars,DISK_READS_TOTAL disk_t,
BUFFER_GETS_TOTAL buffer_t ,ROWS_PROCESSED_TOTAL rows_t ,cpu_time_total cpu_t,
round(elapsed_time_total/1000000) elapsed_s ,iowait_total
from dba_hist_sqlstat
where snap_id between 18085 and 18086
and sql_id in ('<NUMERO_DO_SQLID_1>','<NUMERO_DO_SQLID_2>') /
- Coletar número de cursores abertos no Banco de Dados, e cursores em utilização
select user_process username,
"Recursive Calls",
"Opened Cursors",
"Current Cursors"
from (
select nvl(ss.USERNAME,'ORACLE PROC')||'('||se.sid||') ' user_process,
sum(decode(NAME,'recursive calls',value)) "Recursive Calls",
sum(decode(NAME,'opened cursors cumulative',value)) "Opened Cursors",
sum(decode(NAME,'opened cursors current',value)) "Current Cursors"
from v\$session ss,
v\$sesstat se,
v\$statname sn
where se.STATISTIC# = sn.STATISTIC#
and (NAME like '%opened cursors current%'
or NAME like '%recursive calls%'
or NAME like '%opened cursors cumulative%')
and se.SID = ss.SID
and ss.USERNAME is not null
group by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') '
)
orasnap_user_cursors
order by USER_PROCESS,"Recursive Calls"
/
Resolvendo eventos Cursor Pin S e Cursor Pin S wait on X
Resolver este evento não é uma tarefa que envolve apenas um comando ou apenas um parâmetro. Deve ser feita uma verificação completa do Banco de Dados (Através dos scripts acima demonstrados) juntamente com análise de parâmetros e workload do sistema.
Existem algumas situações onde são identificados Bugs ou correções para problemas específicos, como por exemplos nos Notes abaixo do MOS (My Oracle Support):
- Bug 9694101 - Hang / deadlock between "cursor: pin S wait on X" and "library cache lock" involving dictionary objects [ID 9694101.8]
- "Cursor: Pin S Wait On X" Contention Mutex Sleep Reason Primarily ' kkslce [KKSCHLPIN2]' [ID 1268724.1]
- Bug 7234778 - Unnecessary "cursor: pin S wait on X" waits [ID 7234778.8]
entre outros..
A seguir alguns itens que podem ser verificados para minimizar ou solucionar os eventos de espera Cursor: Pin S e Cursor: Pin S wait on X
- Desabilitar a utilização de Mutex
Como citado acima, esse recurso foi implementado na versão 10gR2, porém pode ser desabilitado no Oracle Database. De qualquer forma MUITA ATENÇÃO ao executar esta alteração, pois o comportamento do Banco de Dados pode se alterar durante a execução de cursores. **P.S: Essa alteração é recomendada na versão 10gR2 para o HP-UX, conforme Note do MOS: (216205.1). Na maioria das plataformas não existe essa necessidade.
_kks_use_mutex_pin=FALSE
alter system set "_kks_use_mutex_pin"=false scope=spfile;
shutdown immediate
startup
/
- Executar um FLUSH na Buffer Cache
Ao executar um FLUSH na Database Buffer Cache todos os blocos de objetos alocados em memória serão descarregados, sendo assim, possíveis situações de HOT BLOCKS em memória serão limpos. Isso pode ajudar na liberação de novos mutexes
alter system flush buffer_cache;
- Validar gerenciamento de Cursores
Existe uma relação com os eventos de espera em Cursor Pin S com os parâmetros open_cursors e session_cached_cursors. É muito importante garantir que os mesmos estejam corretamente definidos.
open_cursors : Número máximo de Cursores abertos simultaneamente no Banco de Dados. session_cached_cursors : Número máximo de Cursores que podem ser gerenciados em cache por sessão.
alter system set open_cursors=1000 scope=both;
alter system set session_cached_cursors=50 scope=both;
- Validar código da aplicação
É importante ainda lembrar que se muitos cursores são abertos ao mesmo tempo para solucionar uma mesma tarefa em um mesmo objeto, o evento de espera Cursor Pin S poderá ocorrer. Sendo assim o código de sua aplicação deve ser otimizado utilizando cursores em momentos estratégicos e de maneira coerente com a real necessidade do aplicativo.
Conclusões
Os eventos de espera são constantes em um Banco de Dados Oracle e devem ser interpretados não como problemáticos porém como pontos de monitoramento e atenção, visando manter o sistema com performance satisfatória e comportamento adequado. Neste artigo pudemos observar o comportamento e funcionamento de eventos de espera em Curores, bem como maneiras de solucionar estes eventos em caso de longa espera. É bastante importante manter o banco de dados sempre atualizado em níveis de PSU e CPU corretos para minimizar possíveis BUGS além de utilizar corretamente os recursos que o Oracle Databas oferece para o desenvolvimento de suas aplicações.
Victor Armbrust é DBA há 10 anos, especialista em Banco de Dados Oracle e Bacharel em Ciências da Computação. Com sólidos conhecimentos em Banco de Dados e Sistemas operacionais, possui certificações OCP 10g/11g, OCE 11g Performance Tuning, OCE 10g RAC, Exadata Implementation Specialist, OCA Mysql 5, LPIC-3, OCA Solaris 10, Data Warehouse Implementation Specialist entre outras. Instrutor Oracle University e Consultor de Banco de Dados na Oracle Advanced Customer Support Services.